﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using IsmsExplorer.Model.Documents;
using IsmsExplorer.Infrastructure.Helper;
using IsmsExplorer.Infrastructure.RepositoryFramework;
using System.Diagnostics;
using System.Data;

namespace IsmsExplorer.Infrastructure.Repositories.Documents {
    /// <summary>
    /// NOTE: IsmsDocumentLink is identified both by a GUID (DocumentID) and an int (ProjectID).
    ///       Hence we need to override the FindBy() and BuildBaseWhereClause(), GetBaseWhereClause().
    /// </summary>
    internal class IsmsDocumentLinkRepository : SqlServerRepositoryBase<IsmsDocumentLink>, IIsmsDocumentLinkRepository {
        #region Data
        #endregion

        #region Constructors
        public IsmsDocumentLinkRepository(IUnitOfWork unitOfWork)
            : base(unitOfWork) {
        }

        public IsmsDocumentLinkRepository()
            : this(null) {
        }
        #endregion

        /// <summary>
        /// In order to find an IsmsDocumentLink, we need both the project ID and document ID.
        /// </summary>
        /// <param name="id">a tuple where first item is the project ID (int) and second item the document ID (Guid)</param>
        /// <returns></returns>
        public override IsmsDocumentLink FindBy(object id) {
            Tuple<int, Guid> key = id as Tuple<int, Guid>;
            Debug.Assert(key != null);
            if (key != null) {
                StringBuilder sb = new StringBuilder(this.GetBaseQuery());
                sb.Append(" ").Append(this.BuildBaseWhereClause(id)).Append(";");
                return this.BuildEntityFromSql(sb.ToString());
            }
            return null;
        }

        #region IIsmsDocumentLinkRepository members

        public IList<IsmsDocumentLink> GetDocumentsForIso27001Control(int projectID, int controlID) {
            StringBuilder sb = new StringBuilder(256);
            sb.AppendFormat(@"SELECT L.{0},L.{1},L.{2},L.{3},L.{4},L.{5},L.{6},L.{7} FROM [IsmsDocumentLinks] L JOIN [Iso27001ControlDocuments] D on L.{8}=D.{9}",
                IsmsDocumentLinkFactory.FieldNames.ID,
                IsmsDocumentLinkFactory.FieldNames.RepositoryTypeID,
                IsmsDocumentLinkFactory.FieldNames.Name,
                IsmsDocumentLinkFactory.FieldNames.URL,
                IsmsDocumentLinkFactory.FieldNames.RepositoryVersionNumber,
                IsmsDocumentLinkFactory.FieldNames.ProjectID,
                IsmsDocumentLinkFactory.FieldNames.DocumentTypeID,
                IsmsDocumentLinkFactory.FieldNames.Description,
                IsmsDocumentLinkFactory.FieldNames.ID,
                Iso27001ControlDocumentationFactory.FieldNames.DocumentID
                );
            sb.AppendFormat(@" WHERE D.{0}={1} AND D.{2}={3}",
                Iso27001ControlDocumentationFactory.FieldNames.ControlID,
                DataHelper.GetSqlValue(controlID),
                Iso27001ControlDocumentationFactory.FieldNames.ProjectID,
                DataHelper.GetSqlValue(projectID)
                );
            return this.BuildEntitiesFromSql(sb.ToString());
        }

        public IsmsDocumentLink FindByUrlInRepository(int projectID, string documentUrl, int repositoryTypeID) {
            StringBuilder sb = new StringBuilder(GetBaseQuery());
            sb.AppendFormat(@" WHERE {0}={1} AND {2}={3} AND {4}={5}",
                IsmsDocumentLinkFactory.FieldNames.ProjectID,
                DataHelper.GetSqlValue(projectID),
                IsmsDocumentLinkFactory.FieldNames.URL,
                DataHelper.GetSqlValue(documentUrl),
                IsmsDocumentLinkFactory.FieldNames.RepositoryTypeID,
                DataHelper.GetSqlValue(repositoryTypeID)
                );
            return this.BuildEntityFromSql(sb.ToString());
        }

        public IList<IsmsDocumentLink> GetDocumentsByTypeID(int projectID, int documentTypeID) {
            // select * from IsmsDocumentLinks where  ID in (select DocumentID from IsmsSpecificDocuments where ProjectID=1 and DocumentTypeID=2)
            StringBuilder sb = new StringBuilder(GetBaseQuery());
            sb.AppendFormat(" WHERE {0}={1} AND {2}={3}",
            IsmsDocumentLinkFactory.FieldNames.ProjectID,
            DataHelper.GetSqlValue(projectID),
            IsmsDocumentLinkFactory.FieldNames.DocumentTypeID,
            DataHelper.GetSqlValue(documentTypeID)
            );
            return this.BuildEntitiesFromSql(sb.ToString());
        }

        public IList<IsmsDocumentLink> GetAllIsmsDocumentLinksForProject(int projectID) {
            StringBuilder sb = new StringBuilder(GetBaseQuery());
            sb.AppendFormat(" WHERE {0}={1}",
            IsmsDocumentLinkFactory.FieldNames.ProjectID,
            DataHelper.GetSqlValue(projectID)
            );
            return this.BuildEntitiesFromSql(sb.ToString());
        }

        #endregion

        #region BuildChildDataCallbacks
        protected override void InitialiseChildCallbacks() {
        }
        #endregion

        #region Child object callbacks
        #endregion

        #region GetBaseQuery
        protected override string GetBaseQuery() {
            return string.Format(@"SELECT {0},{1},{2},{3},{4},{5},{6},{7} FROM [IsmsDocumentLinks]",
                IsmsDocumentLinkFactory.FieldNames.ID,
                IsmsDocumentLinkFactory.FieldNames.RepositoryTypeID,
                IsmsDocumentLinkFactory.FieldNames.Name,
                IsmsDocumentLinkFactory.FieldNames.URL,
                IsmsDocumentLinkFactory.FieldNames.RepositoryVersionNumber,
                IsmsDocumentLinkFactory.FieldNames.DocumentTypeID,
                IsmsDocumentLinkFactory.FieldNames.ProjectID,
                IsmsDocumentLinkFactory.FieldNames.Description
                );
        }
        #endregion

        #region GetBaseWhereClause
        protected override string GetBaseWhereClause() {
            StringBuilder sb = new StringBuilder(" WHERE ");
            sb.Append(IsmsDocumentLinkFactory.FieldNames.ProjectID).Append("={0} AND ").Append(IsmsDocumentLinkFactory.FieldNames.ID).Append("={1}");
            return sb.ToString();
        }
        #endregion

        protected override string BuildBaseWhereClause(object id) {
            Tuple<int, Guid> key = id as Tuple<int, Guid>;
            Debug.Assert(key != null);
            if (key != null) {
                return string.Format(GetBaseWhereClause(), DataHelper.GetSqlValue(key.Item1), DataHelper.GetSqlValue(key.Item2));
            }
            throw new InvalidOperationException("Invalid object key passed for IsmsDocumentLink object");
        }

        #region UnitOfWork implementation
        protected override void PersistDeletedItem(IsmsDocumentLink item) {
            string sql = string.Format(@"DELETE FROM [IsmsDocumentLinks] {0}", this.BuildBaseWhereClause(new Tuple<int, Guid>(item.ProjectID, item.DocumentID)));
            this.Database.ExecuteNonQuery(this.Database.GetSqlStringCommand(sql));
        }

        /// <summary>
        /// Persists an IsmsDocumentLink object.
        /// NOTE: if a DocumentID (Guid) is not provided, one is created and then inserted.
        /// </summary>
        /// <param name="item"></param>
        protected override void PersistNewItem(IsmsDocumentLink item) {
            if (item.DocumentID == Guid.Empty) {
                item.DocumentID = Guid.NewGuid();
            }
            StringBuilder sb = new StringBuilder(256);
            sb.AppendFormat(@"INSERT INTO [IsmsDocumentLinks]({0},{1},{2},{3},{4},{5},{6},{7}) VALUES ({8},{9},{10},{11},{12},{13},{14},{15});",
                IsmsDocumentLinkFactory.FieldNames.ID,
                IsmsDocumentLinkFactory.FieldNames.RepositoryTypeID,
                IsmsDocumentLinkFactory.FieldNames.Name,
                IsmsDocumentLinkFactory.FieldNames.URL,
                IsmsDocumentLinkFactory.FieldNames.RepositoryVersionNumber,
                IsmsDocumentLinkFactory.FieldNames.ProjectID,
                IsmsDocumentLinkFactory.FieldNames.DocumentTypeID,
                IsmsDocumentLinkFactory.FieldNames.Description,
                DataHelper.GetSqlValue(item.DocumentID),
                DataHelper.GetSqlValue(item.RepositoryTypeID),
                DataHelper.GetSqlValue(item.DocumentName),
                DataHelper.GetSqlValue(item.DocumentURL),
                DataHelper.GetSqlValue(item.RepositoryVersion),
                DataHelper.GetSqlValue(item.ProjectID),
                DataHelper.GetSqlValue(item.DocumentTypeID),
                DataHelper.GetSqlValue(item.Description)
                );
            this.Database.ExecuteNonQuery(this.Database.GetSqlStringCommand(sb.ToString()));
        }

        protected override void PersistUpdatedItem(IsmsDocumentLink item) {
            StringBuilder sb = new StringBuilder(@"UPDATE [IsmsDocumentLinks] SET ");
            sb.AppendFormat("{0}={1},", IsmsDocumentLinkFactory.FieldNames.ID, DataHelper.GetSqlValue(item.DocumentID));
            sb.AppendFormat("{0}={1},", IsmsDocumentLinkFactory.FieldNames.RepositoryTypeID, DataHelper.GetSqlValue(item.RepositoryTypeID));
            sb.AppendFormat("{0}={1},", IsmsDocumentLinkFactory.FieldNames.Name, DataHelper.GetSqlValue(item.DocumentName));
            sb.AppendFormat("{0}={1},", IsmsDocumentLinkFactory.FieldNames.URL, DataHelper.GetSqlValue(item.DocumentURL));
            sb.AppendFormat("{0}={1},", IsmsDocumentLinkFactory.FieldNames.RepositoryVersionNumber, DataHelper.GetSqlValue(item.RepositoryVersion));
            sb.AppendFormat("{0}={1},", IsmsDocumentLinkFactory.FieldNames.ProjectID, DataHelper.GetSqlValue(item.ProjectID));
            sb.AppendFormat("{0}={1},", IsmsDocumentLinkFactory.FieldNames.DocumentTypeID, DataHelper.GetSqlValue(item.DocumentTypeID));
            sb.AppendFormat("{0}={1},", IsmsDocumentLinkFactory.FieldNames.Description, DataHelper.GetSqlValue(item.Description));
            // Remove the last unnecessary comma character!
            sb.Length -= ",".Length;
            sb.Append(" ").Append(this.BuildBaseWhereClause(new Tuple<int, Guid>(item.ProjectID, item.DocumentID)));
            this.Database.ExecuteNonQuery(this.Database.GetSqlStringCommand(sb.ToString()));
        }
        #endregion

        #region Private helper/callback methods
        #endregion

    } // end internal class IsmsDocumentLinkRepository : SqlServerRepositoryBase<Asset>, IIsmsDocumentLinkRepository

}
